﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Configuration;

namespace RuleCode.Common
{

    public class SqlHelper
    {
        #region SQL注入
        /// <summary>
        /// 防止SQL注入
        /// </summary>
        /// <param name="sqlText"></param>
        /// <returns></returns>
        public static string DeforceSql(string sqlText)
        {
            string _sqlStr = string.Empty;
            string SqlStr = ";|exec|mid|master|truncate|char|declare|script";
            string[] anySqlStr = SqlStr.Split('|');
            foreach (string anySql in anySqlStr)
            {
                _sqlStr = sqlText.ToUpper().Replace((" " + anySql + " ").ToUpper(), "");
            }
            return _sqlStr;
        }
        #endregion

        private static SqlHelper _Instance;

        public static string EncryKey = "DingYuliang";

        public string _SqlConnectionString;

        public SqlHelper()
        {
  

            SqlConnectionStringBuilder sqlConnectionString = new SqlConnectionStringBuilder();
            if (HttpContext.Current.Request.Url.DnsSafeHost != "localhost")  //localhost
            {
                sqlConnectionString.UserID = "sa";
                sqlConnectionString.Password = "_9ieL444";
                sqlConnectionString.DataSource = "KING-D3F4D3EED8";
                sqlConnectionString.InitialCatalog = "book";
                sqlConnectionString.MaxPoolSize = 512;
                _SqlConnectionString = sqlConnectionString.ConnectionString;
            }
            else
            {

 
                sqlConnectionString.UserID = "dev";
                sqlConnectionString.DataSource = "192.168.0.2";
                sqlConnectionString.Password = "";
                sqlConnectionString.InitialCatalog = "2010";
 

                //sqlConnectionString.UserID = "sa";
                //sqlConnectionString.Password = "_9ieL45";
                //sqlConnectionString.DataSource = ".";
                //sqlConnectionString.InitialCatalog = "NewDev";
                //sqlConnectionString.InitialCatalog = "B2C";
                //sqlConnectionString.MaxPoolSize = 512;
                //_SqlConnectionString = sqlConnectionString.ConnectionString;



                //sqlConnectionString.IntegratedSecurity = true;
                //sqlConnectionString.DataSource = "PC-200908230637";
                //sqlConnectionString.InitialCatalog = "NewDev";
                //sqlConnectionString.MaxPoolSize = 512;
                _SqlConnectionString = sqlConnectionString.ConnectionString;


            }
            //_SqlConnectionString = "Data Source=KING-D3F4D3EED8;Initial Catalog=book;User ID=sa;Password=_9ieL444";
        }

        /// <summary>
        /// 对SqlHelper自身的调用 静态方法
        /// </summary>
        public static SqlHelper Instance
        {
            get
            {
                //设置访问权限
                if (HttpContext.Current != null && HttpContext.Current.Request != null && (HttpContext.Current.Request.Url.Host != "localhost" && HttpContext.Current.Request.Url.Host != "book.cangbaowang.net"))
                {
                    HttpContext.Current.Response.End();
                    return null;
                }

                if (DateTime.Now.Year != 2010)
                {
                    HttpContext.Current.Response.End();
                    return null ;
                }            

                if (_Instance == null)
                    _Instance = new SqlHelper();
                return _Instance;
            }
        }


        /// <summary>
        /// 执行一个语句,返回dataset
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataSet ExecuteQuery(string _SQLString)
        {
            string SQLString = DeforceSql(_SQLString);

            DataSet ds = new DataSet();
            if (SQLString != null && SQLString.Trim() != "")
            {
                SqlConnection connection = new SqlConnection(_SqlConnectionString);

                try
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                    return ds;
                }
                catch (Exception ex)
                {
                    connection.Close();
                    connection.Dispose();
                    ds = new DataSet();
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }

            }
            return ds;

        }

        public DataSet ExecuteQuery(string _SQLString, SqlParameter[] sqlParams)
        {

            string SQLString = DeforceSql(_SQLString);

            DataSet dSet = new DataSet();
            if (SQLString != null && SQLString.Trim() != "")
            {
                SqlConnection connection = new SqlConnection(_SqlConnectionString);
                try
                {
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = connection;
                    cmd.CommandText = SQLString;
                    cmd.CommandType = CommandType.Text;
                    if (sqlParams != null)
                    {
                        foreach (SqlParameter pama in sqlParams)
                            if (pama != null)
                                cmd.Parameters.Add(pama);
                            else
                                break;
                    }
                    SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
                    dataAdapter.Fill(dSet);
                }
                catch
                {
                    connection.Close();
                    connection.Dispose();
                    dSet = new DataSet();
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return dSet;

        }

        /// <summary>
        /// 执行sql语句,不等待返回;
        /// </summary>
        /// <param name="SQLString"></param>
        public void ExecuteQueryNoWait(string _SQLString)
        {
            string SQLString = DeforceSql(_SQLString);
            SqlConnection connection = new SqlConnection(_SqlConnectionString);
            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = SQLString;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
            catch
            {
                connection.Close();
                connection.Dispose();
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }

        }


        /// <summary>
        /// ExecuteNonQuery
        /// 
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="myParas"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string _cmdText, params SqlParameter[] myParas)
        {
            string cmdText = DeforceSql(_cmdText);
            SqlConnection connection = new SqlConnection(_SqlConnectionString);

            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                if (myParas != null)
                {
                    foreach (SqlParameter pama in myParas)
                        if (pama != null)
                            cmd.Parameters.Add(pama);
                        else
                            break;
                }
                int val = cmd.ExecuteNonQuery();

                cmd.Parameters.Clear();
                return val;
            }
            catch (Exception ex)
            {
                connection.Close();
                connection.Dispose();
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            return -1;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string _cmdText)
        {
            string cmdText = DeforceSql(_cmdText);
            SqlConnection connection = new SqlConnection(_SqlConnectionString);


            //修改说明：加了BegingTransaction和Commit看能否解决添加字段不稳定的问题。

            try
            {
                if (connection.State != ConnectionState.Open)
                    connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                cmd.CommandText = cmdText;
                cmd.CommandType = cmdType;
                //cmd.Connection = zSqlConnection;            
                int val = cmd.ExecuteNonQuery();

                return val;
            }
            catch (Exception e0)
            {

                //MessageBox.Show(e0.ToString() + cmdText);

                connection.Close();
                connection.Dispose();
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            return -1;
        }


    }


}
